﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class ContactDAL : IContactDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ContactDAL()
        {

        }
        public IList<ContactInfo> ListContacts(ContactInfo conInfo)
        {
            string strSQL;
            int empSeq = conInfo.Employee == null ? 0 : conInfo.Employee.Sequence;

            List<ContactInfo> infoList = new List<ContactInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", conInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT ec.CONTACT_SEQ, ec.EMP_SEQ, ec.NAME_PREFIX_SEQ, ec.CONTACT_NAME, ec.CONTACT_LAST_NAME"
                        + ", ec.FAM_SEQ, ec.CONTACT_CAREER, ec.CONTACT_ADDRESS, ec.CONTACT_POSTAL_CODE"
                        + ", ec.TEL, ec.MOBILE, ec.EMAIL, ec.DISTRICT_SEQ, ec.CITIZEN"
                        + ", ec.RECORD_STATUS, ec.CREATED_BY, ec.UPDATED_BY"
                    + " FROM HRM_PS_EMP_CONTACTS ec"
                    + " WHERE ec.RECORD_STATUS not like @DeleteRecord"
                    + (conInfo.Sequence > 0 ? " AND ec.CONTACT_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND ec.EMP_SEQ = @empSequence" : "")
                    + " ORDER BY ec.CONTACT_SEQ";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                        empInfo = empList[0];
                    }

                    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo pfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(2)));
                        pfInfo = pfList[0];
                    }

                    MasFamilyHoodDAL famDAL = new MasFamilyHoodDAL();
                    MasFamilyHoodInfo famInfo = new MasFamilyHoodInfo();
                    if (!rdr.IsDBNull(5))
                    {
                        IList<MasFamilyHoodInfo> famList = famDAL.ListFamilyHoods(new MasFamilyHoodInfo(rdr.GetInt32(5)));
                        famInfo = famList[0];
                    }

                    MasDistrictDAL disDAL = new MasDistrictDAL();
                    MasDistrictInfo disInfo = new MasDistrictInfo();
                    if (!rdr.IsDBNull(12))
                    {
                        IList<MasDistrictInfo> disList = disDAL.ListDistricts(new MasDistrictInfo(rdr.GetInt32(12)));
                        disInfo = disList[0];
                    }

                    PersonInfo perInfo = new PersonInfo();
                    perInfo.NamePrefix = pfInfo;
                    perInfo.FirstName = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                    perInfo.LastName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    perInfo.Citizen = rdr.IsDBNull(13) ? "" : rdr.GetString(13);
                    perInfo.Career = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
                    perInfo.FullName = pfInfo.Name + perInfo.FirstName + "  " + perInfo.LastName;

                    ContactInfo Info = new ContactInfo(rdr.GetInt32(0)
                            , empInfo, perInfo, famInfo
                            , new AddressInfo(rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                                , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                                , rdr.IsDBNull(9) ? "" : rdr.GetString(9)
                                , ""
                                , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                                , rdr.IsDBNull(11) ? "" : rdr.GetString(11))
                            , disInfo
                            , rdr.IsDBNull(14) ? "" : rdr.GetString(14)
                            , rdr.IsDBNull(15) ? (int?)null : rdr.GetInt32(15)
                            , rdr.IsDBNull(16) ? (int?)null : rdr.GetInt32(16));
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return infoList;
        }
        public DataSet LoadContacts(Nullable<int> Sequence, Nullable<int> EmpSequence)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@empSequence", EmpSequence);

                strSQL = "SELECT ec.CONTACT_SEQ, ec.EMP_SEQ, ec.NAME_PREFIX_SEQ, ec.CONTACT_NAME, ec.CONTACT_LAST_NAME"
                        + ", ec.FAM_SEQ, ec.CONTACT_CAREER, ec.CONTACT_ADDRESS, ec.CONTACT_POSTAL_CODE"
                        + ", ec.TEL, ec.MOBILE, ec.EMAIL, ec.DISTRICT_SEQ, ec.CITIZEN"
                        + ", ec.RECORD_STATUS, ec.CREATED_BY, ec.UPDATED_BY"
						+ ", CONCAT(ISNULL(np.PREFIX_NAME, ''), ISNULL(ec.CONTACT_NAME, ''), ' ', ISNULL(ec.CONTACT_LAST_NAME, '')) AS FULL_NAME"
                        + ", CONCAT(ISNULL(ec.CONTACT_ADDRESS, ''), ' ', ISNULL(d.DISTRICT_NAME, ''), ' '"
                            + ", ISNULL(a.AMP_NAME, ''), ' ', ISNULL(p.PROV_NAME, '')"
                            + ", ' ', ISNULL(ec.CONTACT_POSTAL_CODE, '')"
                        + ") AS ADDRESS"
                        + ", f.FAM_NAME, a.PROV_SEQ, d.AMP_SEQ, np.PREFIX_NAME"
                        + ", a.AMP_NAME, p.PROV_NAME, d.DISTRICT_NAME"
                    + " FROM HRM_PS_EMP_CONTACTS ec"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON ec.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN MAS_DISTRICTS d ON ec.DISTRICT_SEQ = d.DISTRICT_SEQ"
                        + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                        + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                        + " LEFT JOIN MAS_FAMILYHOOD f ON ec.FAM_SEQ = f.FAM_SEQ"
                    + " WHERE ec.RECORD_STATUS = @ActiveRecord"
                    + (Sequence.HasValue ? " AND ec.CONTACT_SEQ = @Sequence" : "")
                    + (EmpSequence.HasValue ? " AND ec.EMP_SEQ = @empSequence" : "")
                    + " ORDER BY ec.CONTACT_SEQ";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert(ContactInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@FirstName", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@FamilyHood", Info.FamilyHood == null ? (int?)null : Info.FamilyHood.Sequence);
                dbManager.AddParameters(5, "@Career", Info.Person.Career);
                dbManager.AddParameters(6, "@Address", Info.Address.Address);
                dbManager.AddParameters(7, "@PostalCode", Info.Address.PostalCode);
                dbManager.AddParameters(8, "@District", Info.District == null ? (int?)null : Info.District.Sequence);
                dbManager.AddParameters(9, "@Tel", Info.Address.Phone);
                dbManager.AddParameters(10, "@Mobile", Info.Address.Mobile);
                dbManager.AddParameters(11, "@Email", Info.Address.Email);
                dbManager.AddParameters(12, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(13, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(15, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_CONTACTS(EMP_SEQ, CITIZEN"
                        + ", NAME_PREFIX_SEQ, CONTACT_NAME, CONTACT_LAST_NAME, FAM_SEQ"
                        + ", CONTACT_CAREER, CONTACT_ADDRESS, DISTRICT_SEQ, CONTACT_POSTAL_CODE"
                        + ", TEL, MOBILE, EMAIL, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@EmpSeq, @Citizen, @PrefixSeq, @FirstName, @LastName"
                        + ", @FamilyHood, @Career, @Address, @District, @PostalCode"
                        + ", @Tel, @Mobile, @Email, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(ContactInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@FirstName", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@FamilyHood", Info.FamilyHood == null ? (int?)null : Info.FamilyHood.Sequence);
                dbManager.AddParameters(5, "@Career", Info.Person.Career);
                dbManager.AddParameters(6, "@Address", Info.Address.Address);
                dbManager.AddParameters(7, "@PostalCode", Info.Address.PostalCode);
                dbManager.AddParameters(8, "@District", Info.District == null ? (int?)null : Info.District.Sequence);
                dbManager.AddParameters(9, "@Tel", Info.Address.Phone);
                dbManager.AddParameters(10, "@Mobile", Info.Address.Mobile);
                dbManager.AddParameters(11, "@Email", Info.Address.Email);
                dbManager.AddParameters(12, "@Sequence", Info.Sequence);
                dbManager.AddParameters(13, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(14, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(15, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_CONTACTS"
                    + " SET CITIZEN = @Citizen"
                        + ", NAME_PREFIX_SEQ = @PrefixSeq"
                        + ", CONTACT_NAME = @FirstName"
                        + ", CONTACT_LAST_NAME = @LastName"
                        + ", FAM_SEQ = @FamilyHood"
                        + ", CONTACT_CAREER = @Career"
                        + ", CONTACT_ADDRESS = @Address"
                        + ", DISTRICT_SEQ = @District"
                        + ", CONTACT_POSTAL_CODE = @PostalCode"
                        + ", TEL = @Tel"
                        + ", MOBILE = @Mobile"
                        + ", EMAIL = @Email"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CONTACT_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_CONTACTS"
                    + " SET RECORD_STATUS = @RecordStatus"
                    + ", UPDATED_BY = @UpdatedBy"
                    + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CONTACT_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
